	<?php
	// Assuming teacher_id is stored in session after login
	$query = "SELECT DISTINCT class FROM `teachers_classes` WHERE teacher_id = $teacher_id";
	$run_query = mysqli_query($connection, $query);

	if (mysqli_num_rows($run_query) > 0) {
		while ($result = mysqli_fetch_assoc($run_query)) {
			$classes = $result['class'];
			echo "<option value='{$classes}'>{$classes}</option>";
		}
	} else {
		echo "<option>No classes assigned</option>";
	}
	?>


	<?php
		///////////// POST ACTION TO DISPLAY CURRENT SESSION //////////////
		$query = " SELECT * FROM `current_season` ";
		$run_query = mysqli_query($connection, $query);
		if(mysqli_num_rows($run_query) == 1){
			while($result = mysqli_fetch_assoc($run_query)){
				$show_current_session = $result['current_session'];
				echo"
					<option>{$show_current_session}</option>
				";
			}
		}
	?>



	<?php
		///////////// POST ACTION TO DISPLAY CURRENT TERM //////////////
		$query = " SELECT * FROM `current_season` ";
		$run_query = mysqli_query($connection, $query);
		if(mysqli_num_rows($run_query) == 1){
			while($result = mysqli_fetch_assoc($run_query)){
				$show_current_term = $result['current_term'];
				echo"
					<option>{$show_current_term}</option>
				";
			}
		}
	?>

///// SQL TO UPDATE ALL SUBJECT RANK
WITH ranked AS (
    SELECT 
        id, 
        RANK() OVER (PARTITION BY class, subjects, term ORDER BY subject_total DESC) AS subject_rank_val
    FROM results1
)
UPDATE results1 r
JOIN ranked ON r.id = ranked.id
SET r.subject_rank = ranked.subject_rank_val;

/// SQL to update all positions
UPDATE positions p
JOIN (
    SELECT 
        id, 
        RANK() OVER (PARTITION BY class, term ORDER BY students_sub_average DESC) AS student_positions
    FROM positions
) ranked ON p.id = ranked.id
SET p.class_position = ranked.student_positions;




///TO RANK ALL SUBJECTS ACCORDINGLY
<?php
$sql = "
    UPDATE results1 r
    JOIN (
        SELECT id, 
               RANK() OVER (PARTITION BY class, subject ORDER BY subject_total DESC) AS rank
        FROM results1
    ) AS ranked
    ON r.id = ranked.id
    SET r.subject_rank = ranked.rank;
";

if ($conn->query($sql) === TRUE) {
    echo "Subject ranks updated successfully!";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>
